﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.Reflection;
using System.Collections.Specialized;
using System.Configuration;
using System.Data.OracleClient;
using System.ComponentModel;
using System.IO;
using System.Text.RegularExpressions;


namespace ZebraCoreLib.DBHelper
{
    public enum DataBaseType
    {
        Access,
        SqlServer,
        Oracle,
        MySql
    }
    public class SQLHelper
    {

        protected string ConnectionString { get; set; }
        protected DataBaseType DBType { get; set; }
        private static SQLHelper helper;
       // private static CSDBHelper cshelper;
        private static bool iscs = false;
        private static bool hasRead = false;

        private Hashtable TransationPoor = new Hashtable();


        protected SQLHelper() { }

        /// <summary>
        /// 创建单例模式
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static SQLHelper CreateInstance()
        {
            //getConfig();
            //if (!iscs)
            //{
                if (helper == null)
                {
                    helper = new SQLHelper();
                    helper.ConnectionString = "server=218.80.226.140;database=Zebra_DB;uid=sa;pwd=lanny@aaeexpress.com;Min Pool Size=5;Max Pool Size=100";//ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; //global::Portal.Framework.Settings.Default.ConnectionString;
                    helper.DBType = DataBaseType.SqlServer;//(DataBaseType)"Oracle";
                }
                return helper;
            //}
            //else
            //{
            //    if (cshelper == null)
            //    {
            //        string fileName = "config.ini";
            //        StreamReader reader = new StreamReader(fileName);
            //        string connStr = reader.ReadLine();
            //        cshelper = new CSDBHelper();
            //        cshelper.ConnectionString = connStr;
            //        cshelper.DBType = DataBaseType.SqlServer;//(DataBaseType)"Oracle";
            //        reader.Close();
            //    }
            //    return cshelper;
            //}
        }

        //private static void getConfig()
        //{
        //    if (!hasRead)
        //    {
        //        string fileName = "config.ini";
        //        if (File.Exists(fileName))
        //        {
        //            iscs = true;
        //        }
        //        else
        //        {
        //            iscs = false;
        //        }
        //        hasRead = true;
        //    }
        //}

        public virtual DbTransaction BeginTransaction()
        {
            DbConnection transConnection = CreateConnection();
            DbTransaction transaction = transConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
            return transaction;
        }

        //public virtual DbTransaction Transaction
        //{
        //    get
        //    {
        //        return null;
        //        //return System.Web.HttpContext.Current.Session["transactionpobj"] as DbTransaction;
        //    }
        //    set { System.Web.HttpContext.Current.Session["transactionpobj"] = value; }
        //}
        //public DbTransaction Transaction
        //{
        //    get { return System.Web.HttpContext.Current.Session["transactionpobj"] as DbTransaction; }
        //    set { System.Web.HttpContext.Current.Session["transactionpobj"] = value; }
        //}

        public virtual void Commit(DbTransaction transation)
        {
            transation.Commit();
            GC.Collect();
        }

        public virtual void Rollback(DbTransaction transation)
        {
            transation.Rollback();
            GC.Collect();
        }

        public virtual void CloseConnection(DbConnection connection)
        {
            //if (Transaction == null)
                connection.Close();
        }
        /// <summary>
        /// 创建连接
        /// </summary>
        /// <returns></returns>
 
        protected DbConnection CreateConnection()
        {

            DbConnection connection = null;
            switch (DBType)
            {
                case DataBaseType.Access:
                    connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
                    break;
                case DataBaseType.MySql:
                    connection = new System.Data.OleDb.OleDbConnection(ConnectionString);
                    break;
                case DataBaseType.Oracle:
                    connection = new System.Data.OracleClient.OracleConnection(ConnectionString);
                    break;
                case DataBaseType.SqlServer:
                    connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
                    break;
            }
            connection.Open();



            return connection;
        }


        public DbCommand CreateCommand()
        {
            DbCommand command = CreateConnection().CreateCommand();
            //command.Transaction = Transaction;
            return command;
        }

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected DbCommand CreateCommand(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = sqlSegment;
            command.CommandType = commandType;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            //command.Transaction = Transaction;
            return command;
        }

        /// <summary>
        /// 创建适配器
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected DbDataAdapter CreateAdapter(DbConnection connection, string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
            command.CommandText = sqlSegment;
            //command.CommandType = commandType;
            //if (parameters != null)
            //    command.Parameters.AddRange(parameters);

            DbDataAdapter adapter = null;
            switch (DBType)
            {
                case DataBaseType.Access:
                    adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
                    break;
                case DataBaseType.MySql:
                    adapter = new System.Data.OleDb.OleDbDataAdapter((System.Data.OleDb.OleDbCommand)command);
                    break;
                case DataBaseType.Oracle:
                    adapter = new System.Data.OracleClient.OracleDataAdapter((System.Data.OracleClient.OracleCommand)command);
                    break;
                case DataBaseType.SqlServer:
                    adapter = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command);
                    break;
            }
            return adapter;
        }


        public virtual T ExecuteScalar<T>(string sqlSegment)
        {
            return ExecuteScalar<T>(sqlSegment, null);
        }
        public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteScalar<T>(sqlSegment, null, CommandType.Text);
        }

        public virtual T ExecuteScalar<T>(string sqlSegment, CommandType commandType)
        {
            return ExecuteScalar<T>(sqlSegment, null, commandType);
        }
        public virtual T ExecuteScalar<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            //try
            {
                DbConnection connection = CreateConnection();
                DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
                T result = (T)command.ExecuteScalar();
                command.Parameters.Clear();
                CloseConnection(connection);
                return result;
            }
            //catch (Exception ex) { throw new Exception("执行sql语句时发生错误："+ )}
        }

        public int GetAutoID(string table_name)
        {
            DbConnection connection = CreateConnection();

            DbCommand command = CreateCommand(connection, "GetAutoIntID", null, CommandType.StoredProcedure);
            DbParameter parameter1 = command.CreateParameter();
            parameter1.ParameterName = "@table_name";
            parameter1.Value = table_name;

            DbParameter parameter_ret = command.CreateParameter();
            parameter_ret.ParameterName = "@maxid" + table_name;
            parameter_ret.Direction = ParameterDirection.ReturnValue;
            parameter_ret.DbType = DbType.Int32;



            command.Parameters.Add(parameter1);
            command.Parameters.Add(parameter_ret);

            command.ExecuteNonQuery();
            int result = (Int32)parameter_ret.Value;
            command.Parameters.Clear();
            CloseConnection(connection);
            return result;
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数，用于增/删/改数据
        /// </summary>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual int ExecuteNonQuery(string sqlSegment)
        {
            return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
        }
        public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteNonQuery(sqlSegment, null, CommandType.Text);
        }
        public virtual int ExecuteNonQuery(string sqlSegment, CommandType commandType)
        {
            return ExecuteNonQuery(sqlSegment, null, commandType);
        }

        public virtual int ExecuteNonQuery(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            DbConnection connection = CreateConnection();
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
            int result = command.ExecuteNonQuery();
            command.Parameters.Clear();
            CloseConnection(connection);
            return result;
        }


        /// <summary>
        /// 执行SQL语句，返回一张表，用于查询数据
        /// </summary>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>

        public virtual DataTable ExecuteDataTable(string sqlSegment)
        {
            return ExecuteDataTable(sqlSegment, null);
        }
        public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteDataTable(sqlSegment, null, CommandType.Text);
        }
        public virtual DataTable ExecuteDataTable(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {

            DataTable dataTable = new DataTable();
            DbConnection connection = CreateConnection();
            DbDataAdapter adapter = CreateAdapter(connection, sqlSegment, parameters, commandType);
            adapter.Fill(dataTable);
            adapter.SelectCommand.Parameters.Clear();
            CloseConnection(connection);
            return dataTable;

        }

        /// <summary>
        /// 执行SQL语句，返回一个Reader对象，用于查询数据
        /// </summary>
        /// <param name="sqlSegment"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>

        //public virtual DbDataReader ExecuteReader(string sqlSegment)
        //{
        //    return ExecuteReader(sqlSegment, null);
        //}
        //public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters)
        //{
        //    return ExecuteReader(sqlSegment, null,CommandType.Text);
        //}
        //public virtual DbDataReader ExecuteReader(string sqlSegment, IDataParameter[] parameters,CommandType commandType)
        //{
        //    DbConnection connection = CreateConnection();
        //    DbCommand command = CreateCommand(connection, sqlSegment, parameters,commandType);
        //    DbDataReader reader = command.ExecuteReader();
        //    return reader;
        //}

        public virtual List<T> ExecuteObject<T>(string sqlSegment)
        {
            return ExecuteObject<T>(sqlSegment, null);
        }
        public virtual List<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters)
        {
            return ExecuteObject<T>(sqlSegment, parameters, CommandType.Text);
        }
        public virtual List<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType)
        {
            return ExecuteObject<T>(sqlSegment, parameters, commandType, null);
        }
        public virtual List<T> ExecuteObject<T>(string sqlSegment, IDataParameter[] parameters, CommandType commandType, string entity_type)
        {
           // Logger loger = LogManager.GetLogger("biz_log");
           // loger.Info(" Connetion" + " " + sqlSegment + "]");
            DbConnection connection = CreateConnection();
            DbCommand command = CreateCommand(connection, sqlSegment, parameters, commandType);
           // loger.Info(" Connetion" + " " + sqlSegment + "]");
            List<T> entities = new List<T>();
            //try
            //{
            PropertyInfo[] properties = Activator.CreateInstance<T>().GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            using (DbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    T entity = Activator.CreateInstance<T>();

                    //if(properties==null)
                    //    properties = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

                    foreach (var property in properties)
                    {

                        object value = null;
                        
                        try
                        {
                            value = reader[property.Name];
                        }
                        catch { continue; }
                        object new_value = null;
                        if (value == DBNull.Value)
                        {
                            if (property.PropertyType == typeof(int) ||
                                property.PropertyType == typeof(Int32) ||
                                property.PropertyType == typeof(decimal) ||
                                property.PropertyType == typeof(float) ||
                                property.PropertyType == typeof(double)

                                )
                                value = 0;
                        }
                        if (property.PropertyType == value.GetType())
                            new_value = value;
                        else if (property.PropertyType == typeof(bool))
                            new_value = value == DBNull.Value ? false : (value.ToString() == "0" ? false : true);

                        else
                            new_value = TypeDescriptor.GetConverter(property.PropertyType).ConvertFrom(value.ToString());


                        if (property.PropertyType == typeof(DateTime?) && new_value == null)
                            new_value = DBNull.Value;
                        else if (new_value.GetType() == typeof(string) && new_value.ToString() == "")
                            new_value = "";
                        if (new_value.GetType() == typeof(decimal) && (decimal)new_value != 0m && new_value.ToString().IndexOf(".") > 0)
                        {
                            string str_new_value = new_value.ToString();
                            str_new_value = Regex.Replace(str_new_value, "0+$", "");
                            new_value = decimal.Parse(str_new_value);
                        }

                        property.SetValue(entity, new_value == DBNull.Value ? null : new_value, null);
                    }
                    entities.Add(entity);
                }
                reader.Close();
                CloseConnection(connection);
            }

            command.Parameters.Clear();
           // loger.Info(" finish" + " " + sqlSegment + "]");
            return entities;

        }

    }
}
